/**
 * Copyright 2010 Kurtis Nusbaum
 *
 * This file is part of RemoteRunner.  
 *
 * RemoteRunner is free software: you can 
 * redistribute it and/or modify it under the terms of the GNU General Public 
 * License as published by the Free Software Foundation, either version 2 of the 
 * License, or (at your option) any later version.  
 *
 * RemoteRunner is distributed in the hope that it will be useful, but WITHOUT 
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License 
 * for more details.  You should have received a copy of the GNU  General 
 * Public License along with RemoteRunner. If not, see 
 * http://www.gnu.org/licenses/.
 */

package org.klnusbaum.remoterunner;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.database.Cursor;
import android.content.ContentValues;
import android.util.Log;

import java.util.ArrayList;

/**
 * Class used for storing host information
 */
class HostsDB{
	/** Constant containing defualt port number. */
  public static final int defaultPortNumber = 22;

	/** Constant representing an invalid host id */
	public static final int INVALID_HOST_ID = -1;

	/** Constant representing an invalid command id */
	public static final int INVALID_COMMAND_ID = -1;

	/** Tag used for logging purposes. */
  private static final String TAG = "HostsDatabase";

	/** Name of the database */
  private static final String DATABASE_NAME = "hostsdb";
	/** Database version number */
  private static final int DATABASE_VERSION = 1;
	/** Name of the hosts table. */
  private static final String HOSTS_TABLE_NAME = "hosts";
	/** Name of the commands table. */
  private static final String COMMANDS_TABLE_NAME = "commands";
  
	/** Constants used for various column names */
	static final String COLUMN_NAME = "name";
  static final String COLUMN_HOSTNAME = "hostname";
  static final String COLUMN_USERNAME = "username";
  static final String COLUMN_COMMAND = "command";
  static final String COLUMN_COMMAND_DESC = "description";
	static final String KEY_ID = "_id";
	static final String PARENT_HOST_ID = "parenthost";

	/** Query for getting a list of all hosts */
  private static final String HOSTS_QUERY = 
    "SELECT * FROM " + HOSTS_TABLE_NAME;

	/** SQL statement for creating the hosts table. */
  private static final String HOSTS_TABLE_CREATE = 
    "CREATE TABLE " + HOSTS_TABLE_NAME + "("+
		KEY_ID + " INTEGER PRIMARY KEY, " +
		COLUMN_NAME + " TEXT NOT NULL, " +
    COLUMN_HOSTNAME + " TEXT UNIQUE, " + 
    COLUMN_USERNAME + " TEXT NOT NULL " + ");";

	/** SQL statement for creating the commands table. */
  private static final String COMMANDS_TABLE_CREATE = 
    "CREATE TABLE "+ COMMANDS_TABLE_NAME + " (" +
    KEY_ID + " INTEGER PRIMARY KEY, " + 
		COLUMN_COMMAND + " TEXT, " +
		PARENT_HOST_ID + " INTEGER NOT NULL, "+
		COLUMN_COMMAND_DESC + " TEXT, " + 
    "FOREIGN KEY(" + PARENT_HOST_ID + " ) REFERENCES " + 
    HOSTS_TABLE_NAME + "(" + KEY_ID + ") ON DELETE CASCADE);";

	/** Helper for opening up the actual database. */
  private final HostsDBOpenHelper dbOpenHelper;

	/** A writable database instance. */
  private SQLiteDatabase writableDB;
	/** A readable database instance. */
  private SQLiteDatabase readableDB;

	/**
	 * Constructor for creating a HostsDB object.
   *
	 * @param context Context in which the database is being used.
	 */
  public HostsDB(Context context){
    dbOpenHelper = new HostsDBOpenHelper(context);
    writableDB = dbOpenHelper.getWritableDatabase();
    readableDB = dbOpenHelper.getReadableDatabase();
  }

	/** Close the database. */
	public void close(){
		writableDB.close();
		readableDB.close();
	}


	/**
 	 * Get a cursor of pointing to all of the hosts in the database.
	 * 
 	 * @return Cursor pointing to all of the hosts.
	 */
	public Cursor getHosts(){
		return readableDB.rawQuery(HOSTS_QUERY, null);
	}

	/** Get a Curor pointing to all of the commands for a particular host.
	 *
	 * @param hostID ID of the host for which a list of commands is desired.
	 * @return A cursor pointing to all of the commands for a particular host.
	 */
	public Cursor getCommands(int hostID){
		return readableDB.rawQuery("Select * from " + COMMANDS_TABLE_NAME + 
			" where " + PARENT_HOST_ID + "='" + hostID + "'", 
			null);
	}

	/**
	 * Get the name for an associated hostID.
	 *
	 * @param hostID ID of the host for which a name is desired.
	 * @return The name asscoiated with the given hostID.
	 */
	public String getName(int hostID){
		String toReturn = "";
    Cursor name = readableDB.query(
      HOSTS_TABLE_NAME,
      new String[] {COLUMN_NAME},
      KEY_ID + "='" + hostID + "'",
      null, null, null, null
    );
    if(!name.moveToFirst()){
      Log.e(TAG, "Name not found for host with id: " + hostID + "\n");
    }
    else{
      toReturn = name.getString(0);
    }
		name.close();
		return toReturn;
	}

	/**
	 * Get the hostname for an associated hostID.
	 *
	 * @param hostID ID of the host for which a hostname is desired.
	 * @return The hostname asscoiated with the given hostID.
	 */
	public String getHostname(int hostID){
		String toReturn = "";
    Cursor hostname = readableDB.query(
      HOSTS_TABLE_NAME,
      new String[] {COLUMN_HOSTNAME},
      KEY_ID + "='" + hostID + "'",
      null, null, null, null
    );
    if(!hostname.moveToFirst()){
      Log.e(TAG, "Hostname not found for host with id: " + hostID + "\n");
    }
    else{
      toReturn = hostname.getString(0);
    }
		hostname.close();
		return toReturn;
	}

	/**
	 * Get the username for an associated hostID.
	 *
	 * @param hostID ID of the host for which a username is desired.
	 * @return The username asscoiated with the given hostID.
	 */
  public String getUsername(int hostID){
		String toReturn = null;
    Cursor username = readableDB.query(
      HOSTS_TABLE_NAME,
      new String[] {COLUMN_USERNAME},
      KEY_ID + "='" + hostID + "'",
      null, null, null, null
    );
    if(!username.moveToFirst()){
      Log.e(TAG, "Username not found for host with ID: " + hostID + "\n");
    }
    else{
      toReturn = username.getString(0);
    }
		username.close();
		return toReturn;
  }

	/**
	 * Get the command for an associated commandID.
	 *
	 * @param commandID ID of the desired command.
	 * @return The command associated with the given commandID.
	 */
  public String getCommand(int commandID){
		String toReturn = null;
    Cursor command = readableDB.query(
      COMMANDS_TABLE_NAME,
      new String[] {COLUMN_COMMAND},
      KEY_ID + "='" + commandID + "'",
      null, null, null, null
    );
    if(!command.moveToFirst()){
      Log.e(TAG, "Command not found for commandID: " + commandID + "\n");
    }
    else{
      toReturn = command.getString(0);
    }
		command.close();
		return toReturn;
  }

	/**
	 * Get the command description for an associated commandID.
	 *
	 * @param commandID ID of the desired command description.
	 * @return The command description associated with the given commandID.
	 */
	public String getCommandDescription(int commandID){
		String toReturn = null;
    Cursor commandDesc = readableDB.query(
      COMMANDS_TABLE_NAME,
      new String[] {COLUMN_COMMAND_DESC},
      KEY_ID + "='" + commandID + "'",
      null, null, null, null
    );
    if(!commandDesc.moveToFirst()){
      Log.e(TAG, "Command Description not found for commandID: " + commandID + "\n");
    }
    else{
      toReturn = commandDesc.getString(0);
    }
		commandDesc.close();
		return toReturn;
	}

	/**
	 * Add a host to the database.
	 *
	 * @param hostname Name of the new host.
	 * @param username Username to be used with the new host.
	 * @param portnumber Portnumber to be used with the host.
	 * @return The id of the newly inserted host. If the insertion is 
			unsucessful a -1 is returned.
	 */
  public int addHost(String name, String hostname, String username){
    ContentValues hostToAdd = new ContentValues();
    hostToAdd.put(COLUMN_NAME, name);
    hostToAdd.put(COLUMN_HOSTNAME, hostname);
    hostToAdd.put(COLUMN_USERNAME, username);
    if(writableDB.insert(HOSTS_TABLE_NAME, null, hostToAdd) < 0){
			return -1;
		}
		else{
			Cursor newHostId = readableDB.rawQuery("Select " + KEY_ID + 
				" from " + HOSTS_TABLE_NAME + 
				" where " + COLUMN_HOSTNAME + "='" + hostname + "'",
				null);
			newHostId.moveToFirst();
			int toReturn = newHostId.getInt(0);
			newHostId.close();
			return toReturn;
		}
  }

	/**
	 * Add a command to the database.
	 * 
	 * @param hostID Id of the host for which the command should be associated.
	 * @param command The command to be added.
	 * @param commandDesc A description of the command.
	 * @return The row into which the command was inserted.
	 */
  public long addCommand(int hostID, String command, String commandDesc){
		Log.i(TAG, "Adding command \"" + command +"\" for host with ID\""+
			hostID + "\" to database");
    ContentValues commandToAdd = new ContentValues();
    commandToAdd.put(PARENT_HOST_ID, hostID );
    commandToAdd.put(COLUMN_COMMAND, command);
    commandToAdd.put(COLUMN_COMMAND_DESC, commandDesc);
    return writableDB.insert(
      COMMANDS_TABLE_NAME, null, commandToAdd);
  }

	/** 
	 * Delete a host from the database.
	 * 
	 * @param hostID The ID of the host to delete.
	 * @return The number of hosts that were deleted.
	 * Returns -1 if there was an error.
	 */
  public long deleteHost(int hostID){
		Log.i(TAG, "Deleting host with ID\"" + hostID + "\" from hostsDatabase");
		writableDB.delete(
			COMMANDS_TABLE_NAME, PARENT_HOST_ID + "='" + hostID + "'", null);
    return writableDB.delete(
      HOSTS_TABLE_NAME, KEY_ID + "='" + hostID + "'", null);
  }

	/** 
	 * Delete a command from the database.
	 * 
	 * @param commandID The ID of the command to delete.
	 * @return The number of commands that were deleted.
	 */
	public int deleteCommand(int commandID){
		Log.i(TAG, "Deleting command with ID \"" + commandID);
    return writableDB.delete(
      COMMANDS_TABLE_NAME, 
			KEY_ID + "='" + commandID + "'", 
			null);
	} 

	/**
	 * Update a command.
	 *
	 * @param commandID The ID of a command to update.
	 * @param newCommandName The new name of the command.
	 * @param commandDesc The new description for the command.
	 * @return The number of commands that were updated.
	 */
	public int updateCommand(int commandID, String newCommandName, 
		String commandDesc)
	{
		ContentValues newCommandNameValues = new ContentValues();
		newCommandNameValues.put(COLUMN_COMMAND, newCommandName);
		newCommandNameValues.put(COLUMN_COMMAND_DESC, commandDesc);
		return writableDB.update(
			COMMANDS_TABLE_NAME,
			newCommandNameValues,
			KEY_ID + "='" + commandID + "'",
			null);
	}

	/**
	 * Update a host.
	 *
	 * @param hostID The ID of a host to update.
	 * @param hostname The new name of the host.
	 * @param username The new username to be used with the host.
	 * @param portNumber The new port number to be used with the host.
	 * @return The number of commands that were updated.
	 */
	public int updateHost(int hostID, String name, String hostname, 
		String username)
	{
		ContentValues newHostValues = new ContentValues();
		newHostValues.put(COLUMN_NAME, name);
		newHostValues.put(COLUMN_HOSTNAME, hostname);
		newHostValues.put(COLUMN_USERNAME, username);
		return writableDB.update(
			HOSTS_TABLE_NAME,
			newHostValues,
			KEY_ID + "='" + hostID + "'",
			null);
	}

	/**
	 * A class for helping open a HostsDB.
	 */
  private class HostsDBOpenHelper extends SQLiteOpenHelper{

		/** Actual database that is opened. */
    private SQLiteDatabase hostsDB;
		/** Context in which the database is opened. */
    private final Context helperContext;

		/**
		 * Constructs a new HostsDBOpenHelper object.
		 *
	 	 * @param context The context in which the HostsDBOpenHelper is used.
	 	 */
    HostsDBOpenHelper(Context context){
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      helperContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db){
      hostsDB = db;
      hostsDB.execSQL(HOSTS_TABLE_CREATE);
      hostsDB.execSQL(COMMANDS_TABLE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){

    }
  }
}
